package com.bz.xtcx.manager.mapper.provider;

import com.bz.xtcx.manager.entity.CmsMeeting;
import com.bz.xtcx.manager.persist.conditions.MeetingCondition;
import com.bz.xtcx.manager.utils.WzDateUtil;
import com.bz.xtcx.manager.utils.WzStrUtil;
import org.apache.ibatis.jdbc.SQL;

public class CmsMeetingProvider {
    public String insertSelectiveProvider(CmsMeeting cm) {
        return new SQL() {
            {
                INSERT_INTO("cms_meeting");
                VALUES("id", "#{id}");
                VALUES("code", "#{code}");
                VALUES("type_id", "#{typeId}");
                VALUES("fromer", "#{fromer}");
                VALUES("fromer_contacts_name", "#{fromerContactsName}");
                VALUES("fromer_contacts_phone", "#{fromerContactsPhone}");
                if (WzStrUtil.isNotBlank(cm.getFromerContactsMail())) {
                    VALUES("fromer_contacts_mail", "#{fromerContactsMail}");
                }
                if (WzStrUtil.isNotBlank(cm.getFromerContactsAddr())) {
                    VALUES("fromer_contacts_addr", "#{fromerContactsAddr}");
                }
                VALUES("targeter", "#{targeter}");
                if (WzStrUtil.isNotBlank(cm.getApplicationContentId())) {
                    VALUES("application_content_id", "#{applicationContentId}");
                }
                VALUES("application_note", "#{applicationNote}");
                VALUES("submit_time", "#{submitTime}");
                VALUES("check_status", "#{checkStatus}");
                VALUES("req_mail_status", "#{reqMailStatus}");
                VALUES("req_mobile_status", "#{reqMobileStatus}");
                VALUES("resp_mobile_status", "#{respMobileStatus}");
                VALUES("resp_mail_status", "#{respMailStatus}");
                VALUES("req_mail_cnt", "#{reqMailCnt}");
                VALUES("req_mobile_cnt", "#{reqMobileCnt}");
                VALUES("resp_mobile_cnt", "#{respMobileCnt}");
                VALUES("resp_mail_cnt", "#{respMailCnt}");
                if (WzStrUtil.isNotBlank(cm.getCheckContent())) {
                    VALUES("check_content", "#{checkContent}");
                }
                if (null != cm.getCheckTime()) {
                    VALUES("check_time", "#{checkTime}");
                }
                VALUES("refuse_count", "#{refuseCount}");
                VALUES("creater", "#{creater}");
                VALUES("updater", "#{updater}");
            }
        }.toString();
    }

    public String updateByPrimaryKeySelectiveProvider(CmsMeeting cm) {
        String sqlStr = new SQL() {
            {
                UPDATE("cms_meeting");
                if (null != cm.getTargeter()) {
                    SET("targeter = #{targeter}");
                }
                if (null != cm.getFromerContactsName()) {
                    SET("fromer_contacts_name = #{fromerContactsName}");
                }
                if (null != cm.getFromerContactsPhone()) {
                    SET("fromer_contacts_phone = #{fromerContactsPhone}");
                }
                if (null != cm.getFromerContactsMail()) {
                    SET("fromer_contacts_mail = #{fromerContactsMail}");
                }
                if (null != cm.getFromerContactsAddr()) {
                    SET("fromer_contacts_addr = #{fromerContactsAddr}");
                }
                if (null != cm.getApplicationNote()) {
                    SET("application_note = #{applicationNote}");
                }
                if (null != cm.getCheckStatus()) {
                    SET("check_status = #{checkStatus}");
                }
                if (null != cm.getReqMailStatus()) {
                    SET("req_mail_status = #{reqMailStatus}");
                }
                if (null != cm.getReqMobileStatus()) {
                    SET("req_mobile_status = #{reqMobileStatus}");
                }
                if (null != cm.getRespMailStatus()) {
                    SET("resp_mail_status = #{respMailStatus}");
                }
                if (null != cm.getRespMobileStatus()) {
                    SET("resp_mobile_status = #{respMobileStatus}");
                }
                if (null != cm.getReqMailCnt()) {
                    SET("req_mail_cnt = #{reqMailCnt}");
                }
                if (null != cm.getReqMobileCnt()) {
                    SET("req_mobile_cnt = #{reqMobileCnt}");
                }
                if (null != cm.getRespMailCnt()) {
                    SET("resp_mail_cnt = #{respMailCnt}");
                }
                if (null != cm.getRespMobileCnt()) {
                    SET("resp_mobile_cnt = #{respMobileCnt}");
                }
                if (WzStrUtil.isNotBlank(cm.getCheckContent())) {
                    SET("check_content = #{checkContent}");
                }
                if (null != cm.getCheckTime()) {
                    SET("check_time = #{checkTime}");
                }
                if (null != cm.getRefuseCount()) {
                    SET("refuse_count = #{refuseCount}");
                }
                if (null != cm.getUpdater()) {
                    SET("updater = #{updater}");
                }
                WHERE("id = #{id}");
            }
        }.toString();
        return sqlStr;
    }

    public String countByConditionProvider(MeetingCondition mc) {
        StringBuilder sql = new StringBuilder("select count(DISTINCT id) ");
        sql.append("from cms_meeting ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getId())) {
            sql.append("and id = '" + mc.getId() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getCode())) {
            sql.append("and code = '" + mc.getCode() + "' ");
        }
        if (null != mc.getTypeId()) {
            sql.append("and type_id = " + mc.getTypeId() + " ");
        }
        if (WzStrUtil.isNotBlank(mc.getFromer())) {
            sql.append("and fromer = '" + mc.getFromer() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getTargeter())) {
            sql.append("and targeter = '" + mc.getTargeter() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getApplicationContentId())) {
            sql.append("and application_content_id = '" + mc.getApplicationContentId() + "' ");
        }
        if (null != mc.getCheckStatus()) {
            sql.append("and check_status = " + mc.getCheckStatus() + " ");
        }
        if (null != mc.getSubmitTimeStart()) {
            sql.append("and submit_time >= '" + WzDateUtil.dt2Str(WzDateUtil.dayStart(mc.getSubmitTimeStart()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getSubmitTimeEnd()) {
            sql.append("and submit_time <= '" + WzDateUtil.dt2Str(WzDateUtil.dayEnd(mc.getSubmitTimeEnd()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getCheckTimeStart()) {
            sql.append("and check_time >= '" + WzDateUtil.dt2Str(WzDateUtil.dayStart(mc.getCheckTimeStart()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getCheckTimeEnd()) {
            sql.append("and check_time <= '" + WzDateUtil.dt2Str(WzDateUtil.dayEnd(mc.getCheckTimeEnd()), WzDateUtil.DT_FMT_5) + "' ");
        }
        return sql.toString();
    }

    public String selectByConditionProvider(MeetingCondition mc) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number ");
        sql.append("from cms_meeting a ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getId())) {
            sql.append("and a.id = '" + mc.getId() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getCode())) {
            sql.append("and a.code = '" + mc.getCode() + "' ");
        }
        if (null != mc.getTypeId()) {
            sql.append("and a.type_id = " + mc.getTypeId() + " ");
        }
        if (null != mc.getCheckStatus()) {
            sql.append("and a.check_status = " + mc.getCheckStatus() + " ");
        }
        if (WzStrUtil.isNotBlank(mc.getFromer())) {
            sql.append("and a.fromer = '" + mc.getFromer() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getTargeter())) {
            sql.append("and a.targeter = '" + mc.getTargeter() + "' ");
        }
        if (WzStrUtil.isNotBlank(mc.getApplicationContentId())) {
            sql.append("and a.application_content_id = '" + mc.getApplicationContentId() + "' ");
        }
        if (null != mc.getSubmitTimeStart()) {
            sql.append("and a.submit_time >= '" + WzDateUtil.dt2Str(WzDateUtil.dayStart(mc.getSubmitTimeStart()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getSubmitTimeEnd()) {
            sql.append("and a.submit_time <= '" + WzDateUtil.dt2Str(WzDateUtil.dayEnd(mc.getSubmitTimeEnd()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getCheckTimeStart()) {
            sql.append("and a.check_time >= '" + WzDateUtil.dt2Str(WzDateUtil.dayStart(mc.getCheckTimeStart()), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != mc.getCheckTimeEnd()) {
            sql.append("and a.check_time <= '" + WzDateUtil.dt2Str(WzDateUtil.dayEnd(mc.getCheckTimeEnd()), WzDateUtil.DT_FMT_5) + "' ");
        }
        sql.append("order by a.submit_time desc ");
        if (mc.getNeedPaging()) {
            sql.append("limit " + mc.getPageBegin() + ", " + mc.getPageSize());
        }
        return sql.toString();
    }

    public String selectByPrimaryKeyProvider(String id) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
//        sql.append("b.id_number fromer_id_number, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type ");
//        sql.append("c.id_number targeter_id_number, ");
//        sql.append("d.file_name file_name, ");
//        sql.append("d.save_path save_path, ");
//        sql.append("d.req_rel_url req_rel_url, ");
//        sql.append("d.id file_id ");
        sql.append("from cms_meeting a ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
//        sql.append("left join cms_file d on d.owner_type = 2 and d.owner_id = a.id ");
        sql.append("where a.id = '" + id + "' ");
        return sql.toString();
    }

    public String selectByCodeProvider(String code) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
//        sql.append("b.id_number fromer_id_number, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type ");
//        sql.append("c.id_number targeter_id_number ");
        sql.append("from cms_meeting a ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where a.code = '" + code + "' ");
        return sql.toString();
    }

    public String selectOvertimeByConditionProvider(MeetingCondition mc) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time ");
        sql.append("from cms_meeting a ");
        sql.append("where a.submit_time < '" + WzDateUtil.dt2Str(mc.getSubmitTimeEnd(), WzDateUtil.DT_FMT_5) + "' and a.check_status = 0 ");
        return sql.toString();
    }

    public String selectNoticeErrProvider() {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time ");
        sql.append("from cms_meeting a ");
        sql.append("where (a.check_status =0 && ((a.req_mail_status = 0 && a.req_mail_cnt <= 3) ");
        sql.append("or (a.req_mobile_status = 0 && a.req_mobile_cnt <= 3))) ");
        sql.append("or ((a.check_status = 1 or a.check_status = 2 or a.check_status = 3) && ((a.resp_mobile_status = 0 && a.resp_mobile_cnt <= 3) ");
        sql.append("or (a.resp_mail_status = 0 && a.resp_mail_cnt <= 3))) ");
        return sql.toString();
    }

    public String countForUserProvider(MeetingCondition mc) {
        StringBuilder sql = new StringBuilder("select count(DISTINCT dj.id) ");
        sql.append("form ");
        sql.append("( select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("1 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getFromer())) {
            sql.append("and a.fromer = '" + mc.getFromer() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_expert d on a.application_content_id = d.id");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append("and d.id_number = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_requirements d on a.application_content_id = d.id");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append("and d.code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_results d on a.application_content_id = d.id");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append("and d.code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_service d on a.application_content_id = d.id");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append("and d.org_code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append(") dj ");
        sql.append("where 1 = 1 ");
        if (null != mc.getTypeId()) {
            sql.append("and dj.type_id = " + mc.getTypeId() + " ");
        }
        if (null != mc.getCheckStatus()) {
            sql.append("and dj.check_status = " + mc.getCheckStatus() + " ");
        }
        if (null != mc.getIsFormOrToMe()) {
            sql.append("and dj.from_to_me = " + mc.getIsFormOrToMe() + " ");
        }
        sql.append("order by dj.submit_time desc, dj.type_id ");
        if (mc.getNeedPaging()) {
            sql.append("limit " + mc.getPageBegin() + ", " + mc.getPageSize());
        }
        return sql.toString();
    }

    public String selectForUserProvider(MeetingCondition mc) {
        StringBuilder sql = new StringBuilder("select DISTINCT * ");
        sql.append("from ");
        sql.append("( select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("1 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getFromer())) {
            sql.append(" and a.fromer = '" + mc.getFromer() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_expert d on a.application_content_id = d.id ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append(" and d.id_number = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_requirements d on a.application_content_id = d.id ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append(" and d.code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_results d on a.application_content_id = d.id ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append(" and d.code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append("union ");
        sql.append("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.code code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.fromer fromer, ");
        sql.append("a.fromer_contacts_name fromer_contacts_name, ");
        sql.append("a.fromer_contacts_phone fromer_contacts_phone, ");
        sql.append("a.fromer_contacts_mail fromer_contacts_mail, ");
        sql.append("a.fromer_contacts_addr fromer_contacts_addr, ");
        sql.append("a.targeter targeter, ");
        sql.append("a.application_content_id application_content_id, ");
        sql.append("a.application_note application_note, ");
        sql.append("a.submit_time submit_time, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.req_mail_status req_mail_status, ");
        sql.append("a.req_mobile_status req_mobile_status, ");
        sql.append("a.resp_mobile_status resp_mobile_status, ");
        sql.append("a.resp_mail_status resp_mail_status, ");
        sql.append("a.req_mail_cnt req_mail_cnt, ");
        sql.append("a.req_mobile_cnt req_mobile_cnt, ");
        sql.append("a.resp_mobile_cnt resp_mobile_cnt, ");
        sql.append("a.resp_mail_cnt resp_mail_cnt, ");
        sql.append("a.check_content check_content, ");
        sql.append("a.check_time check_time, ");
        sql.append("a.refuse_count refuse_count, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.email fromer_email, ");
        sql.append("b.name fromer_name, ");
        sql.append("b.user_type fromer_user_type, ");
        sql.append("b.id_number fromer_id_number, ");
        sql.append("b.user_name fromer_user_name, ");
        sql.append("c.user_name targeter_user_name, ");
        sql.append("c.email targeter_email, ");
        sql.append("c.name targeter_name, ");
        sql.append("c.user_type targeter_user_type, ");
        sql.append("c.id_number targeter_id_number, ");
        sql.append("2 from_to_me ");
        sql.append("from cms_meeting a ");
        sql.append("inner join lib_service d on a.application_content_id = d.id ");
        sql.append("left join bus_user b on a.fromer = b.user_id ");
        sql.append("left join bus_user c on a.targeter = c.user_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(mc.getTargeterCN())) {
            sql.append(" and d.org_code = '" + mc.getTargeterCN() + "' ");
        }
        sql.append(") dj ");
        sql.append("where 1 = 1 ");
        if (null != mc.getTypeId()) {
            sql.append("and dj.type_id = " + mc.getTypeId() + " ");
        }
        if (null != mc.getCheckStatus()) {
            sql.append("and dj.check_status = " + mc.getCheckStatus() + " ");
        }
        if (null != mc.getIsFormOrToMe()) {
            sql.append("and dj.from_to_me = " + mc.getIsFormOrToMe() + " ");
        }
        sql.append("order by dj.submit_time desc, dj.type_id ");
        if (mc.getNeedPaging()) {
            sql.append("limit " + mc.getPageBegin() + ", " + mc.getPageSize());
        }
        return sql.toString();
    }
}
